package com.sh.data.engine.domain.shims.kingbase8.manager;

import com.google.common.collect.Lists;
import com.sh.data.engine.common.exception.BusinessException;
import com.sh.data.engine.domain.shims.db.DbOptions;
import com.sh.data.engine.domain.shims.db.GenericDbManager;
import com.sh.data.engine.domain.shims.db.model.PreviewDataDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain.TblType;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

/**
 * @author: mengzheng.mhc
 * @date: 2024/6/7 10:22
 */
public class Kingbase8Manager extends GenericDbManager {

    public Kingbase8Manager(DbOptions options) {
        super(options);
    }

    static {
        String driverClass = "com.kingbase8.Driver";
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Could not load db driver class: " + driverClass);
        }
    }

    @Override
    public String getQueryVersionSql() {
        return "SELECT VERSION()";
    }

    @Override
    public String getQuerySchemaListSql() {
        String sql =
            "select nspname from pg_namespace where nspname not like 'pg_%' and nspname <> 'information_schema'";
        return sql;
    }

    @Override
    public PreviewDataDomain previewData(String schema, String tableName)
        throws SQLException {
        return previewData(escapeTableName(schema) + "." + escapeTableName(tableName));
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName) throws SQLException {
        String sql =
            " SELECT C.OID,N.NSPNAME,C.RELNAME,C.RELKIND FROM SYS_CLASS C LEFT JOIN SYS_NAMESPACE N ON N.OID = C.RELNAMESPACE WHERE C.RELKIND = 'r' AND N.NSPNAME IN (select a.nspname from sys_namespace a left join sys_user b on a.nspowner = b.usesysid where b.usename = '"
                + dbOptions.getUsername()
                + "' and  nspacl is not NULL AND a.nspname NOT IN ('SYS_CATALOG','INFORMATION_SCHEMA','SYSLOGICAL'))";

        List<TableInfoDomain> list = new LinkedList<>();
        try (Connection connection = getConnection();
             PreparedStatement pstmt = this.createPreparedStatement(connection, sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public boolean hasSchema() {
        return true;
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName, String schema) throws SQLException {
        String sql =
            "SELECT TABLE_NAME,TABLE_SCHEMA "
                + " FROM INFORMATION_SCHEMA.TABLES "
                + " WHERE TABLE_CATALOG = ? "
                + " AND TABLE_SCHEMA = ? ";

        List<TableInfoDomain> list = new LinkedList<>();
        try (Connection connection = getConnection();
             PreparedStatement pstmt = this.createPreparedStatement(connection, sql, dbName, schema);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                TableInfoDomain entity = new TableInfoDomain();
                String tableName = rs.getString("table_name");
                entity.setTableName(tableName);
                entity.setSchema(schema);
                entity.setTblType(TblType.MANAGED_TABLE);
                list.add(entity);
            }
        }
        return list;
    }

    private static TableInfoDomain getTableDomain(String dbName, ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        Set<String> set = new HashSet<>();
        for (int index = 1; index <= meta.getColumnCount(); index++) {
            set.add(meta.getColumnLabel(index));
        }
        TableInfoDomain entity = new TableInfoDomain();
        if (dbName != null) {
            entity.setDbName(dbName);
        }

        if (set.contains("RELNAME")) {
            String tableName = rs.getString("RELNAME");
            entity.setTableName(tableName);
        }

        if (set.contains("NSPNAME")) {
            String schema = rs.getString("NSPNAME");
            entity.setSchema(schema);
        }
        return entity;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName) throws SQLException {
        String sql =
            " SELECT C.OID,N.NSPNAME,C.RELNAME,C.RELKIND FROM SYS_CLASS C LEFT JOIN SYS_NAMESPACE N ON N.OID = C.RELNAMESPACE WHERE C.RELKIND = 'v' AND N.NSPNAME IN (select a.nspname from sys_namespace a left join sys_user b on a.nspowner = b.usesysid where b.usename = '"
                + dbOptions.getUsername()
                + "' and  nspacl is not NULL AND a.nspname NOT IN ('SYS_CATALOG','INFORMATION_SCHEMA','SYSLOGICAL'))";

        List<TableInfoDomain> list = new LinkedList<>();
        try (Connection connection = getConnection();
             PreparedStatement pstmt = this.createPreparedStatement(connection, sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName, String schema) throws SQLException {
        String sql =
            "SELECT TABLE_NAME,TABLE_SCHEMA "
                + " FROM INFORMATION_SCHEMA.VIEWS "
                + " WHERE TABLE_CATALOG = ? "
                + " AND TABLE_SCHEMA = ? ";

        List<TableInfoDomain> list = new LinkedList<>();
        try (Connection connection = getConnection();
             PreparedStatement pstmt = this.createPreparedStatement(connection, sql, dbName, schema);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                TableInfoDomain entity = new TableInfoDomain();
                String tableName = rs.getString("table_name");
                entity.setTableName(tableName);
                entity.setSchema(schema);
                entity.setTblType(TblType.VIEW);
                list.add(entity);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName) throws SQLException {

        if (StringUtils.isBlank(dbName)) {
            throw new BusinessException("数据库不能为空");
        }

        String getTableSql =
            "SELECT TABLE_NAME,TABLE_SCHEMA "
                + " FROM INFORMATION_SCHEMA.TABLES "
                + " WHERE TABLE_CATALOG = ? ";

        List<TableInfoDomain> list = new LinkedList<>();
        try (Connection connection = getConnection();
             PreparedStatement pstmt = this.createPreparedStatement(connection, getTableSql, dbName);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                TableInfoDomain entity = new TableInfoDomain();
                String tableName = rs.getString("table_name");
                entity.setTableName(tableName);
                String schema = rs.getString("table_schema");
                entity.setSchema(schema);
                list.add(entity);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName, String schema)
        throws SQLException {
        if (StringUtils.isBlank(schema)) {
            return getTableAndViewList(dbName);
        }
        List<TableInfoDomain> list = Lists.newArrayList();
        List<TableInfoDomain> tableList = this.getTableList(dbName, schema);
        List<TableInfoDomain> viewList = this.getViewList(dbName, schema);
        list.addAll(tableList);
        list.addAll(viewList);
        return list;
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String tableName)
        throws SQLException {
        String sql =
            "select TABLE_NAME,TABLE_TYPE "
                + "from information_schema.tables TABLE_CATALOG = ? and table_name = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = this.createPreparedStatement(connection, sql, dbName, tableName);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                TableInfoDomain entity = new TableInfoDomain();
                tableName = rs.getString("table_name");
                entity.setTableName(tableName);
                list.add(entity);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String schema, String tableName)
        throws SQLException {
        String sql =
            "select TABLE_NAME "
                + " from information_schema.tables where TABLE_CATALOG = ? and  table_schema = ? and  table_name = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = this.createPreparedStatement(connection, sql, dbName, schema,
                 tableName);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                TableInfoDomain entity = new TableInfoDomain();
                tableName = rs.getString("table_name");
                entity.setTableName(tableName);
                entity.setSchema(schema);
                list.add(entity);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public boolean alterFieldDesc(
        String dbName, String tableName, String fieldName, String fieldDes, boolean isPartition)
        throws SQLException {
        throw new SQLException("暂未实现");
    }
}
